﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using TS.Entities;
using System.Data;
using TS.Commons;
using System.Collections.ObjectModel;
using System.Data.SqlClient;
using System.Globalization;

namespace TS.DataAccess
{

    /// <summary>
    /// Author: VuongPT
    /// </summary>
    public class UserDataAccess
    {
        /// <summary>
        /// Parse To Entity
        /// </summary>
        /// <returns></returns>
        public static User ParseToEntity(IDataRecord dataRecord)
        {
            return new User
            {
                Userid = dataRecord["UserId"].ToStringOrDefault().ToNumeric(),
                UserName = dataRecord["UserName"].ToStringOrDefault(),
                GroupId = dataRecord["GroupId"].ToStringOrDefault().ToNumeric(),
                GroupName = dataRecord["Name"].ToStringOrDefault(),
                Email = dataRecord["Email"].ToStringOrDefault(),
                Sex = dataRecord["Sex"].ToStringOrDefault().ToStringOrDefault().ToNumeric(),
                OwnerId = dataRecord["OwnerId"].ToStringOrDefault().ToNumeric(),
                OwnerName = dataRecord["OwnerName"].ToStringOrDefault(),
                IsLock = dataRecord["IsLock"].ToBool()
            };
        }

        /// <summary>
        /// Get User info
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public User GetUserByUserId(int userId)
        {
            var collection = new Collection<SqlParameter>
                                                      {
                                                          new SqlParameter
                                                              (
                                                              "@UserId",
                                                              userId
                                                              )
                                                      };

            using (IDataReader iDataReader = SqlHelper.ExecuteReader(UIUser.SelectUserByUserIdQuery, collection))
            {
                if (iDataReader.Read())
                {
                    return ParseToEntity(iDataReader);
                }
            }
            return null;
        }

        public User GetUser(string account, string password)
        {
            var collection = new Collection<SqlParameter>
                                                      {
                                                          new SqlParameter
                                                              (
                                                              "@Account",
                                                              account
                                                              ),
                                                          new SqlParameter
                                                              (
                                                              "@Password",
                                                              password
                                                              )
                                                      };

            using (IDataReader iDataReader = SqlHelper.ExecuteReader(UIUser.SelectUserQuery, collection))
            {
                if (iDataReader.Read())
                {
                    return ParseToEntity(iDataReader);
                }
            }
            return null;
        }

        /// <summary>
        /// Insert User
        /// </summary>
        /// <returns></returns>
        public int InsertUser(User user)
        {
            int result = 0;
            if (user != null)
            {
                var parameters = new Collection<SqlParameter>
                                                          {
                                                              new SqlParameter("@Account",user.UserName),
                                                              new SqlParameter("@Password",user.Password),
                                                              new SqlParameter("@Email",user.Email),
                                                              new SqlParameter("@GroupId",user.GroupId),
                                                              new SqlParameter("@Sex",user.Sex)
                                                          };


                using (IDataReader reader = SqlHelper.ExecuteReader(UIUser.InsertUserQuery, parameters))
                {
                    if (reader != null)
                    {
                        while (reader.Read())
                        {
                            result = Convert.ToInt32(reader["UserId"], CultureInfo.CurrentCulture);

                        }
                        return result;
                    }
                }
            }

            return result;
        }

        public int UpdateUser(User user)
        {
            int result = 0;
            if (user != null)
            {
                var parameters = new Collection<SqlParameter>
                                                          {
                                                              new SqlParameter("@UserId",user.Userid),
                                                              new SqlParameter("@Account",user.UserName),
                                                              new SqlParameter("@Password",user.Password),
                                                              new SqlParameter("@Email",user.Email),
                                                              new SqlParameter("@GroupId",user.GroupId),
                                                              new SqlParameter("@IsLock",user.IsLock),
                                                              new SqlParameter("@Sex",user.Sex),
                                                              new SqlParameter("@OwnerId",user.OwnerId)

                                                          };


                using (IDataReader reader = SqlHelper.ExecuteReader(UIUser.UpdateUserByUserIdQuery, parameters))
                {
                    if (reader != null)
                    {
                        while (reader.Read())
                        {
                            result = Convert.ToInt32(reader["UserId"], CultureInfo.CurrentCulture);

                        }
                        return result;
                    }
                }
            }

            return result;
        }

        public int UpdateIsLock(User user)
        {
            int result = 0;
            if (user != null)
            {
                var parameters = new Collection<SqlParameter>
                                                          {
                                                              new SqlParameter("@UserId",user.Userid),
                                                              new SqlParameter("@IsLock",user.IsLock)
                                                          };


                using (IDataReader reader = SqlHelper.ExecuteReader(UIUser.UpdateIsLockByUserIdQuery, parameters))
                {
                    if (reader != null)
                    {
                        while (reader.Read())
                        {
                            result = Convert.ToInt32(reader["UserId"], CultureInfo.CurrentCulture);

                        }
                        return result;
                    }
                }
            }

            return result;
        }

        /// <summary>
        /// Read User Pagging
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="strFilter"></param>
        /// <param name="strOrderBy"></param>
        /// <param name="totalRecord"></param>
        /// <returns></returns>
        public Collection<User> ReadUserPaged(int pageSize, int pageIndex, string strFilter, string strOrderBy,string isLock, out int totalRecord)
        {
            var parameters = new Collection<SqlParameter>
                                                      {
                                                          new SqlParameter("@PageSize",pageSize),
                                                          new SqlParameter("@PageIndex",pageIndex),
                                                          new SqlParameter("@StringFilter",strFilter),
                                                          new SqlParameter("@OrderBy",strOrderBy),
                                                          new SqlParameter("@IsLock",isLock)
                                                      };

            totalRecord = 0;

            //get result
            using (IDataReader reader = SqlHelper.ExecuteReader(UIUser.UserSelectPaged, parameters))
            {
                var result = new Collection<User>();
                while (reader.Read())
                {
                    User user = ParseToEntity(reader);
                    user.Email = Utility.WriteString(user.Email, 20);
                    result.Add(user);
                }
                if (reader.NextResult())
                {
                    if (reader.Read())
                    {
                        totalRecord = reader["TotalRowCount"].ToNumeric();
                    }
                }
                return result;
            }
        }


        public User GetRole(int userId, string page)
        {
            var collection = new Collection<SqlParameter>
                                                      {
                                                          new SqlParameter
                                                              (
                                                              "@UserId",
                                                              userId
                                                              ),
                                                              new SqlParameter
                                                              (
                                                              "@Page",
                                                              page
                                                              )

                                                      };

            using (IDataReader iDataReader = SqlHelper.ExecuteReader(UIUser.SelectUserRole, collection))
            {
                if (iDataReader.Read())
                {
                    return ParseToEntity(iDataReader);
                }
            }
            return null;
        }

    }
}
